/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package vera.reports;

import java.io.*;
import java.math.BigDecimal;
import jxl.*;
import java.util.*;
import jxl.Workbook;
import jxl.write.DateFormat;
import jxl.write.Number;
import jxl.write.DateTime;

import jxl.write.*;
import java.util.Date;
import java.text.SimpleDateFormat;
import vera.hibernate.entity.BillHeader;
import vera.hibernate.entity.BillReceiptLinks;

/**
 *
 * @author Dyi
 */
public class Receivables {
    
    private final String destination = "C:\\VERA\\Reports\\RECEIVABLES_";
    public Receivables(List<BillHeader> billHeaders) {
        try {
            java.text.DateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
            java.text.DateFormat dateFormat3 = new SimpleDateFormat("MMMMM");
            Date date = new Date();

            int i = 0;
            
        	String filename = destination + dateFormat.format(date) + ".xls";
	    	WorkbookSettings ws = new WorkbookSettings();
			WritableWorkbook workbook = Workbook.createWorkbook(new File(filename), ws);
			WritableSheet s = workbook.createSheet("Sheet1", 0);

			writeCell(s, "Receivables for the Month of " + dateFormat3.format(date), null, i++, 0);
                        
                        java.text.DateFormat dateFormat2 = new SimpleDateFormat("MMM dd, yyyy");
			writeCell(s, dateFormat2.format(date), null, i++, 0);

           		i++;

			writeColumnHeaders(s, i++);

                        for (BillHeader billHeader : billHeaders) {
                            
				writeDataSheet(s, billHeader, i++);
			}
                        
			workbook.write();

			workbook.close();
	    } catch (IOException e) {
	      e.printStackTrace();
	    } catch (WriteException e) {
	      e.printStackTrace();
	    }
    }

	private void writeColumnHeaders(WritableSheet s, int row) throws WriteException {
            int col = 0;
            
            WritableCellFormat wcf = new WritableCellFormat();
            wcf.setAlignment(jxl.format.Alignment.CENTRE);
            
            writeCell(s, "CLIENT", wcf, row, col++);
		writeCell(s, "Bill #", wcf, row, col++);
		writeCell(s, "Date (MM/DD/YYYY)", wcf, row, col++);
		writeCell(s, "Particulars", wcf, row, col++);
		writeCell(s, "Amount", wcf, row, col++);
		writeCell(s, "OR #", wcf, row, col++);
		writeCell(s, "Amount", wcf, row, col++);
		writeCell(s, "Balance", wcf, row, col++);
		writeCell(s, "Remarks", wcf, row, col++);
	}

	private void writeDataSheet(WritableSheet s, BillHeader billHeader, int row)
    	throws WriteException
	{
            WritableCellFormat wcfDec = new WritableCellFormat(NumberFormats.FORMAT7);
            wcfDec.setAlignment(jxl.format.Alignment.RIGHT);
            
            int col = 0;
		writeCell(s, billHeader.getClientCd(), null, row, col++);
		writeCell(s, billHeader.getBillNum(), null, row, col++);
		writeCell(s, billHeader.getBillEffDt(), null, row, col++);
		writeCell(s, billHeader.getComments(), null, row, col++);
		writeCell(s, billHeader.getBillNetAmt().doubleValue(), wcfDec, row, col++);

		String orNum = "";
		double amount = 0.0;

		for (BillReceiptLinks billReceiptLink : billHeader.getBillReceiptLinks()) {
			orNum = orNum.concat( ", " + billReceiptLink.getReceiptNum());
			amount = amount + billReceiptLink.getAmt().doubleValue();
		}

                orNum.substring(2);
                
		writeCell(s, orNum, null, row, col++);
		writeCell(s, amount, wcfDec, row, col++);
		writeCell(s, billHeader.getBillNetAmt().doubleValue() - amount, wcfDec, row, col++);
		writeCell(s, billHeader.getComments(), null, row, col++);

    }

       	private void writeCell(WritableSheet sheet, String text, WritableCellFormat cf, int row, int col) throws WriteException {
            Label l = null;
                if (cf != null ) {
                    l = new Label(col, row, text, cf);
                } else {
                    l = new Label(col, row, text);                    
                }
		sheet.addCell(l);
	}

	private void writeCell(WritableSheet sheet, double f, WritableCellFormat wcf, int row, int col) throws WriteException {
            Number n = null;
            
            if (wcf != null) {
                n = new Number(col, row, f);
            } else {
		n = new Number(col, row, f);
            }
            
            sheet.addCell(n);
	}

	private void writeCell(WritableSheet sheet, Date d, WritableCellFormat cf, int row, int col) throws WriteException {
            DateTime dt = null;
            
            if (cf != null) {
                dt = new DateTime(col, row, d, cf);
            } else {
                dt = new DateTime(col, row, d);
            }
            
            sheet.addCell(dt);
	}

    public static void main(String args[]) {
        List<BillHeader> billHeaderList = new ArrayList();
        BillHeader billHeader = new BillHeader(100, "test 1", new Date(), new Date(), "201108", BigDecimal.valueOf(1000), BigDecimal.valueOf(1100), BigDecimal.valueOf(0.10), BigDecimal.valueOf(0), BigDecimal.valueOf(1000), 1,
                "NW", 10, new Date(), "receiver1", "comments 1", 20);
        
        List<BillReceiptLinks> billReceiptLinks = new ArrayList();
        
        BillReceiptLinks billReceiptLink = new BillReceiptLinks();
        billReceiptLink.setBillNum(100);
        billReceiptLink.setAmt(BigDecimal.valueOf(200));
        billReceiptLink.setReceiptNum(300);

        billReceiptLinks.add(billReceiptLink);
        
        billReceiptLink = new BillReceiptLinks();
        billReceiptLink.setBillNum(100);
        billReceiptLink.setAmt(BigDecimal.valueOf(300));
        billReceiptLink.setReceiptNum(310);

        billReceiptLinks.add(billReceiptLink);

        billHeader.setBillReceiptLinks(billReceiptLinks);
        billHeaderList.add(billHeader);
        
    	Receivables receivables = new Receivables(billHeaderList);
    }

}
